# ============================================================================
# Means, Standard Deviations, and Correlations Table
# 描述性统计和相关性表格
# ============================================================================

# Load required libraries
library(readxl)      # For reading Excel files

# ============================================================================
# Step 1: Read the Excel file
# ============================================================================

# Read the Excel file
data <- read_excel("C:/Users/wangm/Nutstore/1/AI & Big Data Group/领导底线心智BLM-工作拖延/Supplemental materials/CC/Supervisor Bottom-Line Mentality_Recoded.xlsx")

# ============================================================================
# Step 2: Calculate composite scores for main variables
# ============================================================================

# Calculate mean scores (composite scores) for the five main variables
cat("Calculating composite scores for main variables...\n")
data$SBL <- rowMeans(data[, c("SBL1", "SBL2", "SBL3", "SBL4")], na.rm = TRUE)
data$RT <- rowMeans(data[, c("RT1", "RT2", "RT3")], na.rm = TRUE)
data$GC <- rowMeans(data[, c("GC1", "GC2", "GC3", "GC4", "GC5")], na.rm = TRUE)
data$JC <- rowMeans(data[, c("JC1", "JC2", "JC3", "JC4", "JC5", "JC6")], na.rm = TRUE)
data$WP <- rowMeans(data[, c("WP1", "WP2", "WP3", "WP4", "WP5", "WP6", "WP7", "WP8")], na.rm = TRUE)

# ============================================================================
# Step 3: Select variables in the specified order
# ============================================================================

# Define variables in the required order
desc_vars <- c("Gender", "Age", "Edu", "Rank", "SStenure",
               "CS", "HS",  # Challenge Stressors, Hindrance Stressors
               "SBL", "GC", "JC", "WP", "RT")

# Check which variables exist in the data
available_vars <- intersect(desc_vars, names(data))
missing_desc_vars <- setdiff(desc_vars, names(data))

if(length(missing_desc_vars) > 0) {
  warning(paste("The following variables are missing:", 
                paste(missing_desc_vars, collapse = ", ")))
  cat("Available variables:", paste(available_vars, collapse = ", "), "\n")
}

# Select available variables in the specified order
desc_data <- data[, available_vars, drop = FALSE]

# Convert to numeric (in case variables are read as character)
desc_data <- as.data.frame(lapply(desc_data, function(x) {
  if(is.numeric(x)) return(x)
  as.numeric(as.character(x))
}))

# Remove rows with missing values
desc_data_complete <- na.omit(desc_data)
cat("\nSample size:", nrow(desc_data_complete), "\n\n")

# ============================================================================
# Step 4: Calculate descriptive statistics
# ============================================================================

# Calculate means and standard deviations
means <- sapply(desc_data_complete, mean, na.rm = TRUE)
sds <- sapply(desc_data_complete, sd, na.rm = TRUE)

# ============================================================================
# Step 5: Calculate correlation matrix and p-values
# ============================================================================

# Calculate correlation matrix
cor_matrix <- cor(desc_data_complete, use = "complete.obs")

# Calculate p-values for correlations
cor_p_matrix <- matrix(NA, nrow = ncol(desc_data_complete), ncol = ncol(desc_data_complete))
colnames(cor_p_matrix) <- colnames(desc_data_complete)
rownames(cor_p_matrix) <- colnames(desc_data_complete)

for (i in 1:ncol(desc_data_complete)) {
  for (j in 1:ncol(desc_data_complete)) {
    if (i != j) {
      test_result <- tryCatch(
        cor.test(desc_data_complete[, i], desc_data_complete[, j]),
        error = function(e) NULL
      )
      if (!is.null(test_result)) {
        cor_p_matrix[i, j] <- test_result$p.value
      }
    } else {
      cor_p_matrix[i, j] <- 1
    }
  }
}

# ============================================================================
# Step 6: Create correlation table with means and SDs
# ============================================================================

n_vars <- length(available_vars)

# Create table matrix
# Columns: Variables, M, SD, and n_vars correlation columns
cor_table <- matrix(NA, nrow = n_vars, ncol = n_vars + 3)
colnames(cor_table) <- c("Variables", "M", "SD", available_vars)
cor_table[, 1] <- available_vars
cor_table[, 2] <- sprintf("%.2f", means[available_vars])
cor_table[, 3] <- sprintf("%.2f", sds[available_vars])

# Fill in correlations (lower triangle) with significance markers
for (i in 1:n_vars) {
  for (j in 1:n_vars) {
    if (i > j) {
      # Lower triangle: show correlation with significance
      cor_val <- cor_matrix[available_vars[i], available_vars[j]]
      p_val <- cor_p_matrix[available_vars[i], available_vars[j]]
      
      # Add significance markers
      sig_marker <- ""
      if (!is.na(p_val)) {
        if (p_val < 0.001) {
          sig_marker <- "***"
        } else if (p_val < 0.01) {
          sig_marker <- "**"
        } else if (p_val < 0.05) {
          sig_marker <- "*"
        }
      }
      
      cor_table[i, j + 3] <- paste0(sprintf("%.3f", cor_val), sig_marker)
    } else if (i == j) {
      # Diagonal: show 1.000
      cor_table[i, j + 3] <- "1.000"
    } else {
      # Upper triangle: blank
      cor_table[i, j + 3] <- ""
    }
  }
}

# Convert to data frame
cor_table_df <- as.data.frame(cor_table)

# ============================================================================
# Step 7: Display and Save Results
# ============================================================================

cat("=", rep("=", 80), "\n", sep = "")
cat("Table 3: Means, Standard Deviations, and Correlations\n")
cat("=", rep("=", 80), "\n\n", sep = "")
cat("Note: * p < 0.05, ** p < 0.01, *** p < 0.001\n\n")

print(cor_table_df)

# Save results
write.csv(cor_table_df, "Table3_Means_SD_Correlations.csv", row.names = FALSE)

cat("\n")
cat("=", rep("=", 80), "\n", sep = "")
cat("Results saved to: Table3_Means_SD_Correlations.csv\n")
cat("=", rep("=", 80), "\n", sep = "")

# ============================================================================
# Step 8: Additional outputs (optional)
# ============================================================================

# Create descriptive statistics table only
desc_stats_table <- data.frame(
  Variables = available_vars,
  M = round(means[available_vars], 2),
  SD = round(sds[available_vars], 2),
  stringsAsFactors = FALSE
)

cat("\nDescriptive Statistics:\n")
print(desc_stats_table)
write.csv(desc_stats_table, "Descriptive_Statistics_Only.csv", row.names = FALSE)

# Create full correlation matrix
full_cor_matrix <- round(cor_matrix, 3)
cat("\nFull Correlation Matrix:\n")
print(full_cor_matrix)
write.csv(full_cor_matrix, "Full_Correlation_Matrix.csv")

# Save correlation p-values
write.csv(round(cor_p_matrix, 4), "Correlation_Pvalues.csv")

cat("\nAdditional files saved:\n")
cat("  - Descriptive_Statistics_Only.csv\n")
cat("  - Full_Correlation_Matrix.csv\n")
cat("  - Correlation_Pvalues.csv\n")

